use master
go 

create database Student
on
(
name='Student',
 filename='D:\text\Student.mdf',
 size=5MB,
 maxsize=50MB,
 filegrowth=10%
)
log on
(
name='Students_log',
 filename='D:\text\Student_log.mdf',
 size=5MB,
 maxsize=50MB,
 filegrowth=10%
)
go

use Student

create table stuInfo
(
stuNO char(10) primary key ,
stuName nvarchar(10),
stuAge tinyint,
stuAddress nvarchar(4),
stuSeat tinyint,
stuSex tinyint,
)
go

create table stuexam
(
examNO int primary key identity(1,1),
stuNO char(10) references stuInfo(stuNO),
writeenExam tinyint,
labExam tinyint
)
go

insert into stuInfo values
('s2501','张秋利','20','美国硅谷','1','1'),
('s2502','李斯文','18','湖北武汉','2','0'),
('s2503','马文才','22','湖南长沙','3','1'),
('s2504','欧阳俊雄','21','湖北武汉','4','0'),
('s2505','梅超风','20','湖北武汉','5','1'),
('s2506','陈旋风','19','美国硅谷','6','1'),
('s2507','陈风','20','美国硅谷','7','0')
go

insert into stuexam values
('s2501','50','70'),
('s2502','60','65'),
('s2503','86','85'),
('s2504','40','80'),
('s2505','70','90'),
('s2506','85','90')
go

select stuNO 学号,stuName 姓名,stuAge 年龄,stuAddress 地址,stuSeat 座位号,stuSex 性别 from stuInfo

select stuName 姓名,stuAge 年龄,stuAddress 地址 from stuInfo

select stuNO as 学号,writeenExam as 笔试,labExam as 机试 from stuexam
select stuNO 学号,writeenExam 笔试,labExam 机试 from stuexam
select 学号=stuNO,笔试=writeenExam,机试=labExam from stuexam

select stuName 姓名,stuAge 年龄,stuAddress 地址, stuName+'@'+stuAddress 邮箱 from stuInfo

select stuNO 学号,writeenExam 笔试,labExam 机试,writeenExam+labExam 总分 from stuexam


select distinct stuAddress from stuInfo

select distinct stuAge 所有年龄 from stuInfo

select top 3 * from stuInfo

select top 4 stuName 姓名,stuSeat 座位号 from stuInfo

select top 50 percent * from stuInfo

select * from stuInfo where stuAddress='湖北武汉' and stuAge=20

select labExam from stuexam where labExam>=60 and labExam<=80 order by labExam DESC

select * from stuInfo where stuAddress in ('湖北武汉','湖南长沙')
select * from stuInfo where stuAddress= '湖北武汉' or stuAddress='湖南长沙'

select writeenExam from stuexam where labExam<=70 or labExam>=90 order by writeenExam ASC

select * from stuInfo where stuAge is null or stuAge=''

select * from stuInfo where stuAge is not null and not stuAge=''

select * from stuInfo where stuName like '张%'

select * from stuInfo where stuAddress like '%湖%'

select * from stuInfo where stuName like '张_'

select * from stuInfo where stuName like '__俊%'

select * from stuInfo  order by stuAge DESC

select * from stuInfo  order by stuAge DESC 
select * from stuInfo  order by stuSeat ASC

select top 1 examNO 考试号, stuNO 学号,writeenExam 笔试,labExam 机试 from stuexam order by writeenExam DESC

select top 1 examNO 考试号, stuNO 学号,writeenExam 笔试,labExam 机试 from stuexam order by labExam ASC
